psycopg2 demo 2

Takeaways

We can use string interpolation to compose a SQL query using python strings.
Two methods for doing so include:

1. Using %s , passing in a tuple as the 2nd argument in cursor.execute()

2. Using named string parameters %(foo)s , passing in a dictionary instead.

Additional Resources

• psycopg2 docs: Basic module usage

Code

import psycopg2

connection = psycopg2.connect('dbname=example')

cursor = connection.cursor()

cursor.execute('DROP TABLE IF EXISTS table2;')

cursor.execute('''
  CREATE TABLE table2 (
    id INTEGER PRIMARY KEY,
    completed BOOLEAN NOT NULL DEFAULT False
  );
''')

cursor.execute('INSERT INTO table2 (id, completed) VALUES (%s, %s);', (1, True))

SQL = 'INSERT INTO table2 (id, completed) VALUES (%(id)s, %(completed)s);'

data = {
  'id': 2,
  'completed': False
}
cursor.execute(SQL, data)

connection.commit()

connection.close()
cursor.close()

Let's practice! Now complete Exercise 2.